Distribution of software upgrades and source code changes has become a familiar routine that provides customers of a software product with increased features, bug fixes, new drivers and other updates to the software product (which may be, for example, application software such as “ORACLE FINANCIALS” that is commonly used by customers to implement finance-related functions in their computers). An object through which software upgrades are distributed is typically referred to as a software patch.
For application software used in large enterprises (such as a publicly-owned corporation), a database is a quintessential element that provides scalable, performance-oriented data storage. Patches for portions of application software that interface to the database may require corresponding modifications to the database itself. Specifically, changes may need to be made to one or more attributes of tables, indexes, or views, e.g. a new column for holding an employee's middle initial may need to be added to a table and/or an existing column (or combination of columns) may need to be indexed to support patches being made to application software.
Unlike code changes related to software written in high level languages (e.g. Java/C/C++), a change to a database needs to be handled carefully to not adversely impact or in any manner corrupt data that is already existing in the database at the time of the change. Enterprise Applications such as Oracle E-Business Suite Applications rarely enforce a rigid patch level (e.g. identified by a release number and/or a version number) for their customers. In other words, customers have the flexibility to adopt different patch levels for products used by them, as per their implementation and configuration. Such scenarios pose additional patching challenges for changes to database components.
Keeping in mind the criticality of preserving existing data, patching of a database currently requires extensive potential impact analysis, including consideration of database object revision history with a goal of correctly upgrading the customer's environment per their patch level. Database patch creation process that is traditionally performed involves human inefficiencies and redundancies, as several Release and Development personnel for different products that constitute the application (such as Oracle's E-Business Suite Applications) perform repetitive analysis. Additionally, in spite of laborious analysis, the results are error prone and are likely to cause failures during patching, on account of missing and/or overlooked object revisions, or failed handling of object dependencies. Yet another reason is a failure in the just-described analysis to anticipate different target database versions that customers may have implemented.
A prior art utility for patching databases, called “ODF”, can be used by application development personnel to capture and propagate metadata definitions of four types of database objects—tables, indexes, sequences and views. A listing of the output generated by the ODF utility is shown in FIG. 6A as metadata 601 for an example of a simple database containing a single object of the type table, which is called “TEST_TABLE” and which has two columns. Note that the listing in FIGS. 6A and 6B is expressed in a non-standard syntax which is proprietary and requires a custom parser specific to the syntax.
Note that the ODF utility captures metadata 601 for only four types of objects identified in the previous paragraph, and for no other objects (e.g. triggers, constraints, queues and policies were not supported). Applicants note that the failure of ODF to support all types of database objects makes it an incomplete solution. Furthermore, the metadata 601 that is captured by ODF for even just these four types of objects is incomplete for another reason. Specifically, Applicants note that many important properties of these database objects (for e.g. Table properties like Partitioned/non-partitioned, Global temporary v/s Permanent, Index organized) were not supported.
Moreover, the above-described partial metadata 601 is extracted by ODF from a set of application-specific tables. Applicants note that the use of application-specific tables, results in at least two issues with ODF. First, the prior art ODF utility additionally requires a CASE (Computer-Aided Software Engineering) data-model repository (such as Oracle Designer) and also other snapshot utilities to propagate metadata definitions 601 from the CASE data-model repository to the database. Second, metadata 601 captured by ODF has the risk of being inaccurate, as it is not captured from the source of truth (system data dictionary) but instead from an intermediate repository.
Also, ODF captures metadata 601 (FIG. 6A) for only a list of those individual objects that are manually identified by a developer of an application for which a patch is being developed. Applicants note that requiring the developer to list all objects whose metadata is to be captured makes ODF prone to human error and results in one or more object definitions being missed. Furthermore, ODF captures the metadata of objects in a non-standard format shown in FIG. 6A. Applicants note that failure to use XML prevents easy data exchange and also does not allow any data transformation/manipulation. Moreover, ODF's stores the metadata for a huge number of mostly unrelated objects in a very large single file. Applicants have found that such bundling of unrelated objects into a single file has an adverse impact on performance because each and every object in the single file is compared to the target database during patching of the target database. Finally, ODF did not use any sophisticated approaches in comparing metadata 601 to the target database.
An alternative to ODF is to manually prepare SQL scripts. There is no concept of a metadata definition file in the context of SQL Scripts. Instead, the SQL scripts execute specific, hard-coded DDL (Data Definition Language) commands like DROP TABLE, CREATE TABLE, etc on the source database to make the object metadata definitions match. It is extremely error-prone as it is difficult for developers to know and predict object metadata definitions, in light of varying patch levels and potential customizations applied at the target database. It is very inefficient, time and resource intensive as it is not a generic solution, and requires repeated analysis and the same DDL commands to be used in multiple SQL scripts for different hard-coded objects. For example, a developer may prepare a SQL script file called “create_test_table.sql” which contains specific, hard-coded DDL (Data Definition Language) commands like DROP TABLE TEST_TABLE, CREATE TABLE TEST_TABLE to create the TEST_TABLE. A listing of SQL is shown in FIG. 6B for the above-described example of table object called “TEST_TABLE” which has two columns.
The drawbacks of SQL scripts are as follows. First, there is no concept of a metadata definition file in the context of SQL Scripts. Therefore, use of SQL scripts does not allow for the archiving and versioning of object metadata definitions in a source code control system. Second, the SQL is not generic because it cannot accept as input an object whose metadata is to be patched. Instead, prior art SQL contains hard-coded references to individual object names (such as “TEST_TABLE” in the example of FIG. 6B) that are used in a specific database. Third, this method is inefficient, time and resource intensive as it requires extensive analysis to be repeated for many sql scripts that handle different database objects. Fourth, this method is very error-prone as it is often difficult to predict the object metadata at a target database on account of varying patch levels and customizations present at the target database.
U.S. Pat. No. 6,785,673 granted to Fernandez et al. on Aug. 31, 2004 and entitled “Method For Converting Relational Data Into XML” is hereby incorporated by reference herein in its entirety. This patent describes converting relational data into an XML document, to construct materialized XML views of relational databases.
See also U.S. Pat. No. 6,732,095 granted to Warshavsky et al. on May 4, 2004 and entitled “Method And Apparatus For Mapping Between XML And Relational Representations” that is also incorporated by reference herein in its entirety. This patent describes a method to convert data between a relational format and an XML document, by creating a set of XML Mapping Definition from metadata; selecting relational data from a relational application database, and converting the relational data to the XML document using the set of XML Mapping Definition.